package com.chris.conference.Repository;

import com.chris.conference.Class.Record;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.sql.Timestamp;
import java.util.List;

public interface RecordRepository extends JpaRepository<Record, Integer>{

    List<Record> findByUserid(String userid);

    List<Record> findByRoomid(String roomid);

    List<Record> findByStatus(String status);

    //查询指定时间段、房间记录
    @Query(value = "SELECT * FROM record WHERE roomid=:roomid AND starttime BETWEEN :starttime AND :endtime", nativeQuery = true)
    List<Record> findByTime(Timestamp starttime, Timestamp endtime, String roomid);

    //查询指定日期间记录
    @Query(value = "SELECT * FROM record WHERE starttime >= date_format(:startdate,'%Y-%m-%d') AND starttime <= date_format(:enddate,'%Y-%m-%d') AND status=:status", nativeQuery = true)
    List<Record> findByDate(String startdate, String enddate, String status);

    //查询指定日期间、房间记录
    @Query(value = "SELECT * FROM record WHERE roomid=:roomid AND starttime >= date_format(:startdate,'%Y-%m-%d') AND starttime <= date_format(:enddate,'%Y-%m-%d') AND status=:status", nativeQuery = true)
    List<Record> findByDate_Roomid(String startdate,String enddate,String roomid, String status);

    //查询指定日期间、用户记录
    @Query(value = "SELECT * FROM record WHERE userid=:userid AND starttime >= date_format(:startdate,'%Y-%m-%d') AND starttime <= date_format(:enddate,'%Y-%m-%d') AND status=:status", nativeQuery = true)
    List<Record> findByDate_Userid(String startdate, String enddate, String userid, String status);

    //查询指定日期间、用户、房间记录
    @Query(value = "SELECT * FROM record WHERE roomid=:roomid AND userid=:userid AND starttime >= date_format(:startdate,'%Y-%m-%d') AND starttime <= date_format(:enddate,'%Y-%m-%d') AND status=:status", nativeQuery = true)
    List<Record> findByDate_Userid_Roomid(String startdate, String enddate, String userid, String roomid, String status);

    //查询一周的记录
    @Query(value = "SELECT * FROM record WHERE YEARWEEK(date_format(starttime,'%Y-%m-%d'))=YEARWEEK(date_format(:date,'%Y-%m-%d')) AND roomid=:roomid AND status = 1", nativeQuery = true)
    List<Record> findByWeek(String date,String roomid);
}
